#include "DbConnector.h"

int DbConnector::databaseId = 0;

void DbConnector::creatPilipiliDatabase()
{
    QSqlDatabase db;

    if (!db.isValid())
    {
        db = QSqlDatabase::addDatabase("QMYSQL");
        db.setHostName("127.0.0.1");          // 连接本地主机
        db.setPort(3306);                     // 端口号，默认的
        db.setDatabaseName("XueHaiTongHang"); // 数据库名
        db.setUserName("root");               // 用户
        db.setPassword("abc123");             // 密码
        if (db.open())
        {
            qDebug() << "数据库连接成功。";
        }
        else
        {
            qDebug() << "数据库连接失败：" << db.lastError().text();
        }

        QSqlQuery query;
        QString sql;

        // 文件表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS UPLOAD_FILE (
        file_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        file_sha512 CHAR(128) NOT NULL UNIQUE,
        file_path VARCHAR(512) NOT NULL
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "文件表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "文件表创建成功。";
        }

        // 用户表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS APP_USER (
        user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        account VARCHAR(64) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        avatar_file_id BIGINT UNSIGNED,
        username VARCHAR(64),
        join_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        bio TEXT,
        FOREIGN KEY (avatar_file_id) REFERENCES UPLOAD_FILE (file_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "用户表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "用户表创建成功。";
        }

        // 文章表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS ARTICLE (
        article_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        author_id BIGINT UNSIGNED NOT NULL,
        title VARCHAR(255) NOT NULL,
        summary TEXT NOT NULL,
        cover_file_id BIGINT UNSIGNED,
        category VARCHAR(64) NOT NULL,
        content LONGTEXT NOT NULL,
        update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        read_count INT UNSIGNED DEFAULT 0,
        attachment_file_id BIGINT UNSIGNED,
        attachment_name VARCHAR(255),
        attachment_price DECIMAL(10, 2) DEFAULT 0,
        push_factor DECIMAL(10, 8),
        FOREIGN KEY (author_id) REFERENCES APP_USER (user_id),
        FOREIGN KEY (cover_file_id) REFERENCES UPLOAD_FILE (file_id),
        FOREIGN KEY (attachment_file_id) REFERENCES UPLOAD_FILE (file_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "文章表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "文章表创建成功。";
        }

        // 评论表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS COMMENT (
        comment_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL,
        article_id BIGINT UNSIGNED NOT NULL,
        comment_time DATETIME DEFAULT CURRENT_TIMESTAMP,
        content TEXT NOT NULL,
        FOREIGN KEY (user_id) REFERENCES APP_USER (user_id),
        FOREIGN KEY (article_id) REFERENCES ARTICLE (article_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "评论表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "评论表创建成功。";
        }

        // 文章举报表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS ARTICLE_REPORT (
        report_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        article_id BIGINT UNSIGNED NOT NULL UNIQUE,
        report_content TEXT NOT NULL,
        is_handled TINYINT (1) DEFAULT 0,
        FOREIGN KEY (article_id) REFERENCES ARTICLE (article_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "文章举报表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "文章举报表创建成功。";
        }

        // 评论举报表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS COMMENT_REPORT (
        comment_report_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        comment_id BIGINT UNSIGNED NOT NULL UNIQUE,
        report_content TEXT NOT NULL,
        is_handled TINYINT (1) DEFAULT 0,
        FOREIGN KEY (comment_id) REFERENCES COMMENT (comment_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "评论举报表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "评论举报表创建成功。";
        }

        // 申诉表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS APPEAL (
        appeal_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL UNIQUE,
        appeal_content TEXT NOT NULL,
        is_handled TINYINT (1) DEFAULT 0,
        FOREIGN KEY (user_id) REFERENCES APP_USER (user_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "申诉表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "申诉表创建成功。";
        }

        // 点赞表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS ARTICLE_LIKE (
        like_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL,
        article_id BIGINT UNSIGNED NOT NULL,
        FOREIGN KEY (user_id) REFERENCES APP_USER (user_id),
        FOREIGN KEY (article_id) REFERENCES ARTICLE (article_id),
        UNIQUE (user_id, article_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "点赞表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "点赞表创建成功。";
        }

        // 收藏表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS FAVORITE (
        favorite_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL,
        article_id BIGINT UNSIGNED NOT NULL,
        FOREIGN KEY (user_id) REFERENCES APP_USER (user_id),
        FOREIGN KEY (article_id) REFERENCES ARTICLE (article_id),
        UNIQUE (user_id, article_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "收藏表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "收藏表创建成功。";
        }

        // 购买记录表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS PURCHASE (
        purchase_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL,
        article_id BIGINT UNSIGNED NOT NULL,
        FOREIGN KEY (user_id) REFERENCES APP_USER (user_id),
        FOREIGN KEY (article_id) REFERENCES ARTICLE (article_id),
        UNIQUE (user_id, article_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "购买记录表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "购买记录表创建成功。";
        }

        // 浏览记录表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS VIEW_HISTORY (
        history_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED,
        article_id BIGINT UNSIGNED NOT NULL,
        FOREIGN KEY (user_id) REFERENCES APP_USER (user_id),
        FOREIGN KEY (article_id) REFERENCES ARTICLE (article_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "浏览记录表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "浏览记录表创建成功。";
        }

        // 关注表
        sql = R"(
CREATE TABLE
    IF NOT EXISTS FOLLOW (
        follow_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        follower_id BIGINT UNSIGNED NOT NULL,
        followed_id BIGINT UNSIGNED NOT NULL,
        FOREIGN KEY (follower_id) REFERENCES APP_USER (user_id),
        FOREIGN KEY (followed_id) REFERENCES APP_USER (user_id),
        UNIQUE (follower_id, followed_id)
    );
        )";
        if (!query.exec(sql))
        {
            qWarning() << "关注表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "关注表创建成功。";
        }
    }

    if (db.isOpen())
        db.close();
}

QSqlDatabase DbConnector::getDatabase()
{
    if (!newDb.isValid())
    {
        newDb = QSqlDatabase::addDatabase("QMYSQL", QString::number(++DbConnector::databaseId));
        newDb.setHostName("127.0.0.1");          // 连接本地主机
        newDb.setPort(3306);                     // 端口号，默认的
        newDb.setDatabaseName("XueHaiTongHang"); // 数据库名
        newDb.setUserName("root");               // 用户
        newDb.setPassword("abc123");             // 密码
        if (newDb.open())
        {
            // qDebug() << "数据库连接成功：" << DbConnector::databaseId;
        }
        else
        {
            qDebug() << "数据库连接失败：" << newDb.lastError().text();
        }
    }
    return newDb;
}

DbConnector::~DbConnector()
{
    if (newDb.isOpen())
        newDb.close();
}
